import baostock as bs
import pandas as pd
import pymysql
import os
from DBUtils.PooledDB import PooledDB

#数据库连接池配置
POOL = PooledDB(
    creator=pymysql,  # 使用链接数据库的模块
    maxconnections=6,  # 连接池允许的最大连接数，0和None表示不限制连接数
    mincached=2,  # 初始化时，链接池中至少创建的空闲的链接，0表示不创建
    maxcached=5,  # 链接池中最多闲置的链接，0和None不限制
    blocking=True,  # 连接池中如果没有可用连接后，是否阻塞等待。True，等待；False，不等待然后报错
    maxusage=None,  # 一个链接最多被重复使用的次数，None表示无限制
    setsession=[],  # 开始会话前执行的命令列表。如：["set datestyle to ...", "set time zone ..."]
    ping=0,
    # ping MySQL服务端，检查是否服务可用。# 如：0 = None = never, 1 = default = whenever it is requested, 2 = when a cursor is created, 4 = when a query is executed, 7 = always
    host='120.55.183.30',
    port=3306,
    user='root',
    password='AAbb147258',
    database='luckystock',
    charset='utf8',
    autocommit='True'
)

def showFile(base_dir):
    files = [os.path.join(base_dir, file) for file in os.listdir(base_dir)]
    return files

def todb(file_path):
    data = pd.read_csv(file_path)
    list = pd.DataFrame(data).values

    conn = POOL.connection()
    cursor = conn.cursor()

    for item in list:
        date = item[0]
        code = item[1].replace("sh.","").replace("sz.","")
        zhangfu = item[12]

        if zhangfu>9.5:
            # 判断是否存在
            cursor.execute("select * from stock_zhangting where code=%s and date=%s", [code, date])
            isExist = cursor.fetchall()
            if len(isExist):
                continue
            # insert 数据库
            cursor.execute("select `name` from stock_stock where code=%s", [code])
            stock = cursor.fetchone()
            if len(stock):
                name = stock[0]
            cursor.execute('insert into stock_zhangting (`code`,`stock`, `date`) values(%s, %s, %s)',[code ,name, date])

    return

# main方法
if __name__ == "__main__":
    files = showFile("D:/my/history/")
    for file in files:
        todb(file)
